/*
 * This file is released under the terms of the Artistic License.  Please see
 * the file LICENSE, included in this package, for details.
 *
 * Copyright (C) 2006 Rilson Nascimento
 *
 * Market Feed transaction
 * ------------------------
 * The Market-Feed transaction receives the latest prices for securities
 * that come in on the market feed ticker.
 *
 * Based on TPC-E Standard Specification Draft Revision 0.32.2e Clause 3.3.9.
 */

/*
 * Frame 1
 * responsible to find the symbol and to modify the row in the LAST_TRADE table
 * for that symbol with the new price, to add the quantity traded to the daily
 * volume, and to modify the last trade date
 */

CREATE OR REPLACE FUNCTION MarketFeedFrame1 (
                        IN max_feed_len   INTEGER,
                        IN price_quote_in NUMERIC(8, 2)[],
                        IN status_submitted VARCHAR(4),
                        IN symbol_in VARCHAR(15)[],
                        IN trade_qty_in INTEGER[],
                        IN type_limit_buy VARCHAR(3),
                        IN type_limit_sell VARCHAR(3),
                        IN type_stop_loss VARCHAR(3),
                        OUT num_updated INTEGER,
                        OUT send_len INTEGER,
                        OUT symbol VARCHAR(15)[],
                        OUT trade_id BIGINT[],
                        OUT price_quote NUMERIC(8, 2)[],
                        OUT trade_qty INTEGER[],
                        OUT trade_type VARCHAR(3)[]
) RETURNS SETOF record AS $$
DECLARE
    i                   INTEGER;
    now_dts             TIMESTAMP;
    request_list        refcursor;
    _trade_id          BIGINT;
    _price_quote       NUMERIC(8,2);
    _trade_type        VARCHAR(3);
    _trade_qty         INTEGER;
    _symbol            VARCHAR(15);

    rows_sent           INTEGER;
    rows_updated        INTEGER;
BEGIN
    now_dts = now();
    num_updated = 0;
    send_len = 0;

    FOR i IN 1..max_feed_len LOOP

        rows_sent = 0;
        rows_updated = 0;

        _symbol = symbol_in[i];
        _price_quote = price_quote_in[i];
        _trade_qty = trade_qty_in[i];

        UPDATE  LAST_TRADE
        SET LT_PRICE = _price_quote,
            LT_VOL = LT_VOL + _trade_qty,
            LT_DTS = now_dts
        WHERE   LT_S_SYMB = _symbol;

        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        num_updated = num_updated + rows_updated;

        OPEN request_list FOR
        SELECT  TR_T_ID,
                TR_BID_PRICE,
                TR_TT_ID,
                TR_QTY
        FROM    TRADE_REQUEST
        WHERE   TR_S_SYMB = _symbol and 
                ((TR_TT_ID = type_stop_loss and TR_BID_PRICE >= _price_quote) or
                (TR_TT_ID = type_limit_sell and TR_BID_PRICE <= _price_quote) or
                (TR_TT_ID = type_limit_buy and TR_BID_PRICE >= _price_quote));

        FETCH   request_list
        INTO    _trade_id,
                _price_quote,
                _trade_type,
                _trade_qty;

        WHILE FOUND LOOP
            UPDATE  TRADE
            SET     T_DTS = now_dts,
                    T_ST_ID = status_submitted
            WHERE   T_ID = _trade_id;
        
            DELETE  FROM TRADE_REQUEST
            WHERE   TR_T_ID = _trade_id;

            INSERT INTO TRADE_HISTORY
            VALUES (_trade_id, now_dts, status_submitted);

            symbol = symbol || _symbol;
            trade_id = trade_id || _trade_id;
            price_quote = price_quote || _price_quote;
            trade_type = trade_type || _trade_type;
            trade_qty = trade_qty || _trade_qty;

            rows_sent = rows_sent + 1;

            FETCH   request_list
            INTO    _trade_id,
                    _price_quote,
                    _trade_type,
                    _trade_qty;
        END LOOP;
    
        CLOSE request_list;
        -- commit transaction
        send_len = send_len + rows_sent;
    END LOOP;

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';

